package com.dabei.excelcodeconvert;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;

public class ExcelToSqlInsert {

    // 假设目标表名为：code_mapping
    private static final String TABLE_NAME = "code_mapping";
    // Excel 文件路径（请根据实际情况修改）
    private static final String EXCEL_FILE_PATH = "码值转换.xlsx";

    public static void main(String[] args) {
        try (FileInputStream fis = new FileInputStream(EXCEL_FILE_PATH);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheet("Sheet1"); // 指定要读取的 sheet 名称

            if (sheet == null) {
                System.err.println("Sheet 'Sheet1' 不存在！");
                return;
            }

            // 跳过标题行（第一行）
            boolean isHeader = true;
            for (Row row : sheet) {
                if (isHeader) {
                    isHeader = false;
                    continue;
                }

                // 读取每一行的列数据
                Cell codeCell = row.getCell(0);
                Cell codeDescCell = row.getCell(1);
                Cell newCodeCell = row.getCell(2);
                Cell newCodeDescCell = row.getCell(3);

                // 获取值，注意处理空值
                String code = getCellValue(codeCell);
                String codeDesc = getCellValue(codeDescCell);
                String newCode = getCellValue(newCodeCell);
                String newCodeDesc = getCellValue(newCodeDescCell);

                // 生成 INSERT SQL 语句
                String sql = String.format(
                    "INSERT INTO %s (code, code_desc, new_code, new_code_desc) VALUES ('%s', '%s', '%s', '%s');",
                    TABLE_NAME, code, codeDesc, newCode, newCodeDesc
                );

                System.out.println(sql);
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 通用方法：安全获取单元格值
    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    // 判断是否为整数（如码值可能是数字）
                    double value = cell.getNumericCellValue();
                    if (value == Math.floor(value)) {
                        return String.valueOf((long) value);
                    } else {
                        return String.valueOf(value);
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }
}